﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DatabaseInterface;
using System.Data.SqlClient;

namespace BaseUnits.Builders
{
    public class PowerPlantBuilder
    {
        Connection connection;
        public PowerPlantBuilder(Connection connection)
        {
            this.connection = connection;
        }
        /// <summary>
        /// Gets the power plant which contains the power unit
        /// </summary>
        /// <param name="powerUnit"></param>
        /// <returns></returns>
        public IPowerPlant getPowerPlant(IPowerUnit powerUnit)
        {
            IPowerPlant powerPlant = null;
            String sqlCommandText;
            SqlConnection cnx = this.connection.getConnection();
            sqlCommandText = "SELECT PowerPlantId, externalId, Description FROM PowerPlants WHERE PowerPlantID in ";
            sqlCommandText += "(SELECT PlantId FROM PlantPowerUnits WHERE PowerUnitId = @PowerUnitId)";
                   
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            cmd.Parameters.Add("@PowerUnitId",System.Data.SqlDbType.Int);
            cmd.Parameters["@PowerUnitId"].Value = powerUnit.ID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DatabaseInterface.DataBase.PowerPlants pl = new DatabaseInterface.DataBase.PowerPlants();
            da.Fill(pl._PowerPlants);
            String externalId;
            String description;
            int powerPlantId;
            IPowerGrid grid;
            PowerUnitBuilder pb = new PowerUnitBuilder(this.connection);

            foreach (DatabaseInterface.DataBase.PowerPlants.PowerPlantsRow row in pl._PowerPlants.Rows)
            {
                powerPlantId = Convert.ToInt32(row["PowerPlantId"]);
                description = Convert.ToString(row["Description"]);
                externalId = Convert.ToString(row["ExternalId"]);
                grid = GetPowerGrid(powerPlantId);

                powerPlant = new PowerPlant(powerPlantId, externalId, description, pb.getPowerUnits((powerPlantId.ToString())));
            }          
            return powerPlant;
        }
        public IPowerPlant getPowerPlant(int powerPlantId)
        {
            IPowerPlant powerPlant = null;
            String sqlCommandText;
            SqlConnection cnx = this.connection.getConnection();
            sqlCommandText = "SELECT PowerPlantId, externalId, Description FROM PowerPlants WHERE PowerPlantID = @PowerPlantId";

            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            cmd.Parameters.Add("@PowerPlantId", System.Data.SqlDbType.Int);
            cmd.Parameters["@PowerPlantId"].Value = powerPlantId;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DatabaseInterface.DataBase.PowerPlants pl = new DatabaseInterface.DataBase.PowerPlants();
            da.Fill(pl._PowerPlants);
            String externalId;
            String description;
            IPowerGrid grid;
            PowerUnitBuilder pb = new PowerUnitBuilder(this.connection);

            foreach (DatabaseInterface.DataBase.PowerPlants.PowerPlantsRow row in pl._PowerPlants.Rows)
            {
                description = Convert.ToString(row["Description"]);
                externalId = Convert.ToString(row["ExternalId"]);
                grid = GetPowerGrid(powerPlantId);

                powerPlant = new PowerPlant(powerPlantId, externalId, description, pb.getPowerUnits((powerPlantId.ToString())));
            }
            return powerPlant;
        }

        public void AddPowerUnit(IPowerPlant powerPlant, IPowerUnit powerUnit)
        {
            String sqlCommandText = "SELECT * FROM PlantPowerUnits WHERE PowerUnitId = @PowerUnitId";
            SqlConnection cnx = this.connection.getConnection();
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            
            cmd.Parameters.Add("@PowerUnitId", System.Data.SqlDbType.Int);
            cmd.Parameters["@PowerUnitId"].Value = powerUnit.ID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            System.Data.DataSet ds = new System.Data.DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count != 0)
            {
                sqlCommandText = "UPDATE PlantPowerUnits SET PlantId = @PowerPlantId WHERE PowerUnitId = @PowerUnitId";
            }
            else
            {
                sqlCommandText = "INSERT INTO PlantPowerUnits VALUES (@PowerPlantId, @PowerUnitId)";
            }

            cmd.CommandText = sqlCommandText;

            cmd.Parameters.Add("@PowerPlantId", System.Data.SqlDbType.Int);
            cmd.Parameters["@PowerPlantId"].Value = powerPlant.PowerPlantId;

            cnx.Open();
            cmd.ExecuteNonQuery();
            cnx.Close();

        }
        public List<IPowerPlant> getPowerPlants(String GridId = "")
        {
            String sqlCommandText;
            SqlConnection cnx = this.connection.getConnection();
            if (GridId == "")
            {
                sqlCommandText = "SELECT PowerPlantId, externalId, Description FROM PowerPlants";
            }
            else
            {
                sqlCommandText = "SELECT PowerPlantId, externalId, Description FROM PowerPlants WHERE PowerPlantID in ";
                sqlCommandText += "(SELECT PlantId FROM GridPlants WHERE GridId ='" + GridId + "')";
            }
            List<IPowerPlant> powerPlants = new List<IPowerPlant>();
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DatabaseInterface.DataBase.PowerPlants pl = new DatabaseInterface.DataBase.PowerPlants();
            da.Fill(pl._PowerPlants);
            String externalId;
            String description;
            int powerPlantId;
            PowerUnitBuilder pb = new PowerUnitBuilder(this.connection);
            PowerPlant newPowerPlant;
            IPowerGrid grid;

            foreach (DatabaseInterface.DataBase.PowerPlants.PowerPlantsRow row in pl._PowerPlants.Rows)
            {
                powerPlantId = Convert.ToInt32(row["PowerPlantId"]);
                description = Convert.ToString(row["Description"]);
                if (row["ExternalId"] == DBNull.Value)
                {
                    externalId = "";
                }
                else
                {
                    externalId = Convert.ToString(row["ExternalId"]);
                }
                //grid = GetPowerGrid(powerPlantId);

                newPowerPlant = new PowerPlant(powerPlantId, externalId, description, pb.getPowerUnits((powerPlantId.ToString())));
                powerPlants.Add(newPowerPlant);
            }
            return powerPlants;
        }

        public IPowerGrid GetPowerGrid(int powerPlantID)
        {
            GridBuilder gridBuilder = new GridBuilder(this.connection);
            return gridBuilder.GetGrid(powerPlantID);
        }
    }
}
